<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Import extends TMT_Controller {

    //DEVELOPMENT PURPOSE ONLY
    /**
     * @TODO: Delete
     *
     * */
    private static $tags = array('CAM',
        'IND',
        'ONJ',
        'STR');
    private static $catalogue_columns = array('Titel der Maßnahme(ohne Tags)',
        'Cluster',
        'Clusterkürzel',
        'Preiskategorie',
        'Preis');
    public static $stope_columns = array('PersNr',
        'Name',
        'Vorname',
        'E-Mail',
        'IST-Jobcluster',
        'PLAN-Jobcluster YE',
        'IST-Expert Group',
        'PLAN-Expert Group YE',
        'OrgE-E6',
        'OrgE-E5',
        'KSt',
        'Ort PTB',
        'MaGr',
        'Bemerkungen',
        'PE-Massnahme',
        'PE-Massnahme2',
        'PE-Massnahme3',
        'Name FüKra');                  //PN FüKra, GoAhead Fachlaufbahn sind nicht drin!!!
    private $errors;
    private $error_log;
    private $data;
    private $notifications;

    /**
     * This function fetches the tag in front of a requirement (STOPE-Import and Catalogue during development phase)
     * @author: Marco Dennstädt
     * @return: array	Array with Tag and Requirement
     * @param: string	Format: TAG - REQUIREMENTNAME
     */
    public static function seperate_requirements($requirement) {
        $returning = NULL;
        $tag = substr($requirement, 0, 3);
        if (in_array($tag, self::$tags)) {
            $returning = array('Tag' => $tag,
                'Requirement' => substr($requirement, 4));
        } else {
            $returning = array('Tag' => NULL,
                'Requirement' => $requirement);
        }

        return $returning;
    }

    function __construct() {
        parent::__construct();

        $this->load->helper(array('html', 'form'));
        $this->load->library('table');

        $this->benchmark->mark('import_start');

        if ($this->session->flashdata('errors')) {
            $this->errors = $this->session->flashdata('errors');
        }
        if ($this->session->flashdata('notifications')) {
            $this->notifications = $this->session->flashdata('notifications');
        }
    }

    /**
     * Standardfunction of this controller
     * @author: Marco Dennstädt
     */
    function index() {
        $this->headline = 'Import';
        $this->show_sidebar = FALSE;
        $this->load->helper('form');
        $this->lang->load('upload', 'deutsch');
        $data['errors'] = $this->errors;
        $data['notifications'] = $this->notifications;

        $first_course = Model\Course::first();
        $data['form']['target'] = 'import/import_data';
        $data['form']['radio_buttons']['Katalogimport'] = array('name' => 'import_type',
            'value' => 'catalogue',
            'checked' => 'checked');

        $data['form']['radio_buttons']['StoPE und Mitarbeiter Import'] = array('name' => 'import_type',
            'value' => 'stope');
        if (!isset($first_course)) {
            $data['form']['radio_buttons']['StoPE und Mitarbeiter Import'] = array_merge($data['form']['radio_buttons']['StoPE und Mitarbeiter Import'], array('disabled' => 'disabled'));
        }

        $data['form']['upload'] = array('name' => 'xml_file');

        $data['form']['submit'] = array('type' => 'submit',
            'name' => 'upload',
            'value' => 'Hochladen');



        $this->view_data = $data;
    }

    /**
     * This function checks the file in the POST variable, uploads the file and saves it in the 'uploads' directory. The final filename is encrypted.
     * @param: string | type of the file which should be uploaded.
     *
     *
     */
    private function upload_file($file_ext) {
        //variable containing file info, will be returned later
        $data = NULL;


        $config['upload_path'] = './uploads/';
        $config['allowed_types'] = '*';
        $config['max_size'] = '0';
        $config['encrypt_name'] = TRUE;

        $this->load->library('upload', $config);
        if ($this->upload->do_upload('xml_file')) {
            $data = $this->upload->data();
            //if file extension is wrong, delete file and return null
            if ($file_ext !== $data["file_ext"]) {
                unlink($data['full_path']);
                $data = NULL;
            }
            //else do nothing, file extension is correct
        }
        //else do nothing, $data will stay NULL

        return $data;
    }

    /**
     * 	This function delegates the file_data to the function who has to insert it
     * 	@author: Marco Dennstädt
     */
    function import_data() {
        $this->load->library('form_validation');

        $this->form_validation->set_rules('upload', '', 'required');
        $this->form_validation->set_rules('import_type', '', 'required');

        if (TRUE == $this->form_validation->run()) {
            $this->data = $this->upload_file(".xml");
            if (isset($this->data)) {
                $parsed_data = $this->parseData(set_value('import_type'));
                //Data is now in the arrays and the file on the server is not needed anymore
                if (isset($this->data)) {
                    unlink($this->data['full_path']);
                    unset($this->data);
                }


                if (isset($parsed_data)) {
                    switch (set_value('import_type')) {
                        case 'catalogue': $success = $this->insert_catalogue_data($parsed_data['data'], $parsed_data['indices']);
                            break;
                        case 'stope' : $success = $this->insert_coworker_data($parsed_data['data'], $parsed_data['indices']);
                            break;
                    }
                    if ($success && empty($this->errors))
                        $this->notifications[] = 'Daten erfolgreich importiert!';
                    else if ($success)
                        $this->notifications[] = 'Daten teilweise importiert!';
                    else
                        $this->errors[] = 'Daten nicht erfolgreich importiert!';
                }
                else {
                    $this->errors[] = 'Eingegebene Daten haben das falsche Format.';
                }
            } else {
                $this->errors[] = 'Keine Datei oder Datei mit falschem Datentyp ausgewählt.<br>Bitte verwenden Sie XML-Daten für den Import.';
            }
        } else {
            //redirect('import');
        }

        if (isset($this->data)) {
            unlink($this->data['full_path']);
            $this->data = NULL;
        }


        $logpath = APPPATH . 'logs/import/';
        $filepath = $logpath . date('Y-d-m') . '_error.php';

        $message = '';
        if (!file_exists($filepath)) {
            $message .= "<" . "?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed'); ?" . ">\n\n";
        }

        foreach ($this->error_log as $error) {
            $message .= $error . "\r\n";
        }

        if (!is_dir($logpath)) {
            @mkdir($logpath, 700, true);
        }


        if ($fp = @fopen($filepath, FOPEN_WRITE_CREATE)) {
            fwrite($fp, $message);
            fclose($fp);
        }

        $this->benchmark->mark('import_end');
        $this->session->set_flashdata('errors', $this->errors);
        $this->session->set_flashdata('notifications', $this->notifications);
        $this->session->set_flashdata('benchmark', $this->benchmark->elapsed_time('import_start', 'import_end'));

        redirect('import');
    }

    private function add_error_message($row, $message) {
        static $last_row = 0;

        $this->error_log[] = 'ERROR - ' . date('Y-m-d H:i:s') . ' --> Import Error: --> Zeile ' . $row . ': ' . $message;

        if ($row != $last_row) {
            $this->errors[] = 'Zeile ' . $row . ":";
            $last_row = $row;
        }

        $this->errors[] = "&nbsp&nbsp&nbsp&nbsp" . $message;
    }

    public function check_coworker_data($data, $indices, $zeile) {
        $return = TRUE;

        setlocale(LC_ALL, 'de_DE.utf8');

        if (count(self::$stope_columns) > count($data)) {
            return FALSE;
        }


        foreach ($indices as $index) {
            if (!isset($data[$index])) {
                $this->add_error_message($zeile, "Nicht alle Werte vorhanden!");
                return FALSE;
            }
        }


        //Personalnummer
        if (is_numeric($data[$indices[self::$stope_columns[0]]]) &&
                $data[$indices[self::$stope_columns[0]]] > 0 &&
                count_chars($data[$indices[self::$stope_columns[0]]] <= 10)) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, self::$stope_columns[0] . ': ' . $data[$indices[self::$stope_columns[0]]] . ' ist keine Zahl!');
        }


        //Name
        $name = preg_match("/^\p{L}*$/iu", $data[$indices[self::$stope_columns[1]]]);

        if ($name == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, self::$stope_columns[1] . ': ' . $data[$indices[self::$stope_columns[1]]] . ' enthält nicht nur Buchstaben!');
        }


        //Vorname
        $vorname = preg_match("/^\p{L}*$/iu", $data[$indices[self::$stope_columns[2]]]);

        if ($vorname == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, self::$stope_columns[2] . ': ' . $data[$indices[self::$stope_columns[2]]] . ' enthält nicht nur Buchstaben!');
        }


        //E-Mail
        if (!filter_var($data[$indices[self::$stope_columns[3]]], FILTER_VALIDATE_EMAIL)) {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige E-Mail: ' . $data[$indices[self::$stope_columns[3]]]);
        }


        //IST-Jobcluster
        $ist_jobcluster = preg_match("/^[A-Za-z äüö0\/]*$/", $data[$indices[self::$stope_columns[4]]]); //  '/' fehlt

        if ($ist_jobcluster == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige Zeichen bei ' . self::$stope_columns[4] . ': ' . $data[$indices[self::$stope_columns[4]]]);
        }



        //PLAN-Jobcluster YE
        $plan_jobcluster = preg_match("/^[A-Za-z öäü0\/]*$/", $data[$indices[self::$stope_columns[5]]]);  //  '/' fehlt

        if ($plan_jobcluster == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige Zeichen bei ' . self::$stope_columns[5] . ': ' . $data[$indices[self::$stope_columns[5]]]);
        }


        //IST-Expert Group
        $ist_expert_group = preg_match("/^[\w ,äüö]*$/", $data[$indices[self::$stope_columns[6]]]);

        if ($ist_expert_group == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige Zeichen bei ' . self::$stope_columns[6] . ': ' . $data[$indices[self::$stope_columns[6]]]);
        }


        //PLAN-Expert Group YE
        $plan_expert_group = preg_match("/^[\w ,äüö]*$/", $data[$indices[self::$stope_columns[7]]]);

        if ($plan_expert_group == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige Zeichen bei ' . self::$stope_columns[7] . ': ' . $data[$indices[self::$stope_columns[7]]]);
        }


        //OrgE-E6
        $OrgE_E6 = preg_match("/^[\w\&äüö ]*$/", $data[$indices[self::$stope_columns[8]]]);

        if ($OrgE_E6 == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige Zeichen bei ' . self::$stope_columns[8] . ': ' . $data[$indices[self::$stope_columns[8]]]);
        }


        //OrgE-E5
        $OrgE_E5 = preg_match("/^[\w\&äüö ]*$/", $data[$indices[self::$stope_columns[9]]]);

        if ($OrgE_E5 == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige Zeichen bei ' . self::$stope_columns[9] . ': ' . $data[$indices[self::$stope_columns[9]]]);
        }


        //KSt
        $KSt = preg_match("/^[0-9]*$/", $data[$indices[self::$stope_columns[10]]]);

        if ($KSt == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige Zeichen bei ' . self::$stope_columns[10] . ': ' . $data[$indices[self::$stope_columns[10]]]);
        }


        //Ort PTB
        $Ort_PTB = preg_match("/^[A-Za-z0äüö]*$/", $data[$indices[self::$stope_columns[11]]]);

        if ($Ort_PTB == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige Zeichen bei ' . self::$stope_columns[11] . ': ' . $data[$indices[self::$stope_columns[11]]]);
        }


        //MaGr
        $MaGr = preg_match("/^[A-Za-z 0äüö]*$/", $data[$indices[self::$stope_columns[12]]]);

        if ($MaGr == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige Zeichen bei ' . self::$stope_columns[12] . ': ' . $data[$indices[self::$stope_columns[12]]]);
        }


        //PE-Massnahme
        $PE_Massnahme = preg_match("/^[\w -0:äüö;.,]*$/", $data[$indices[self::$stope_columns[14]]]);

        if ($PE_Massnahme == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige Zeichen bei ' . self::$stope_columns[14] . ': ' . $data[$indices[self::$stope_columns[14]]]);
        }


        //PE-Massnahme2
        $PE_Massnahme2 = preg_match("/^[\w -0:äüö;.,]*$/", $data[$indices[self::$stope_columns[15]]]);

        if ($PE_Massnahme2 == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige Zeichen bei ' . self::$stope_columns[15] . ': ' . $data[$indices[self::$stope_columns[15]]]);
        }


        //PE-Massnahme3
        $PE_Massnahme3 = preg_match("/^[\w -0:äüö;,.]*$/", $data[$indices[self::$stope_columns[16]]]);

        if ($PE_Massnahme3 == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Ungültige Zeichen bei ' . self::$stope_columns[16] . ': ' . $data[$indices[self::$stope_columns[16]]]);
        }
        
        //Name FüKra
        $name_FüKra = preg_match("/^\p{L}*$/iu", $data[$indices[self::$stope_columns[17]]]);
        
        if ($name_FüKra == 1) {
            
        } else {
            $return = FALSE;
            $this->add_error_message($zeile, 'Name FüKra: ' . $data[$indices[self::$stope_columns[17]]] . ' enthält nicht nur Buchstaben!');
        }
        return $return;
    }

    private function insert_coworker_data($data, $indices) {
        if (!(isset($data) && isset($indices))) {
            return false;
        }

        $this->load->model('import/coworker_data');

        $result = NULL;

        $jobcluster_arr = NULL;
        $expertgroup_arr = NULL;
        $location_arr = NULL;
        $contact_arr = NULL;
        $contact_type_arr = NULL;
        $coworker_arr = NULL;
        $department_arr = NULL;
        $cost_center_arr = NULL;
        $team_arr = NULL;
        $teamrole_arr = NULL;
        $teamleader_arr = NULL;
        $teamleader_arr_tmp = NULL;
        $comment_arr = NULL;

        //to improve performance this step may be done much earlier in the controller
        //because this is the second parse above the data
        //Here we're "creating" the objects which will be inserted later

        $zeile = 2;
        $zaehle_error = 3;

        foreach ($data as $coworker_data) {

            //skip corrupt data
            if (!$this->check_coworker_data($coworker_data, $indices, $zeile++)) {
                $zaehle_error++;
                continue;
            }

            //fetch every cluster in the array, these array has to be shrinked later (unique)
            if ($coworker_data[$indices[self::$stope_columns[4]]] != '0') {
                $jobcluster_arr[] = $coworker_data[$indices[self::$stope_columns[4]]];
            }

            if ($coworker_data[$indices[self::$stope_columns[5]]] != '0') {
                $jobcluster_arr[] = $coworker_data[$indices[self::$stope_columns[5]]];
            }
            //the same with expert groups
            if ($coworker_data[$indices[self::$stope_columns[6]]] != '0') {
                $expertgroup_arr[] = $coworker_data[$indices[self::$stope_columns[6]]];
            }

            if ($coworker_data[$indices[self::$stope_columns[7]]] != '0') {
                $expertgroup_arr[] = $coworker_data[$indices[self::$stope_columns[7]]];
            }

            //and with locations
            $location_arr[] = $coworker_data[$indices[self::$stope_columns[11]]];
            //and departments
            $department_arr[] = $coworker_data[$indices[self::$stope_columns[9]]];
            //and contact types
            $contact_type_arr[] = $coworker_data[$indices[self::$stope_columns[12]]];
            //and cost_centers
            $cost_center_arr[] = $coworker_data[$indices[self::$stope_columns[10]]];

            //This is our contact structure
            $contact = array('E-Mail' => $coworker_data[$indices[self::$stope_columns[3]]],
                'Name' => $coworker_data[$indices[self::$stope_columns[1]]],
                'Vorname' => $coworker_data[$indices[self::$stope_columns[2]]],
                'Ort PTB' => $coworker_data[$indices[self::$stope_columns[11]]], //This has to be exchanged later to the index in the db
                'MaGr' => $coworker_data[$indices[self::$stope_columns[12]]]); //This also has to be exchanged later, same reason
            $contact_arr[$coworker_data[$indices[self::$stope_columns[0]]]] = $contact;



            //This is our coworker structure
            $coworker = array('PersNr' => $coworker_data[$indices[self::$stope_columns[0]]],
                'IST-Jobcluster' => $coworker_data[$indices[self::$stope_columns[4]]], //has to be exchanged
                'PLAN-Jobcluster YE' => $coworker_data[$indices[self::$stope_columns[5]]], //has to be exchanged
                'IST-Expert Group' => $coworker_data[$indices[self::$stope_columns[6]]], //has to be exchanged
                'PLAN-Expert Group YE' => $coworker_data[$indices[self::$stope_columns[7]]], //has to be exchanged
                'KSt' => $coworker_data[$indices[self::$stope_columns[10]]], //has to be exchanged
            );


            $coworker_arr[$coworker_data[$indices[self::$stope_columns[0]]]] = $coworker;

            $team[$coworker_data[$indices[self::$stope_columns[8]]]] = array('department' => $coworker_data[$indices[self::$stope_columns[9]]],
                'costcenter' => $coworker_data[$indices[self::$stope_columns[10]]]);


            $teamrole = array('PersNr' => $coworker_data[$indices[self::$stope_columns[0]]],
                'OrgE-E6' => $coworker_data[$indices[self::$stope_columns[8]]]);
            $teamrole_arr[] = $teamrole;


            if (isset($indices[self::$stope_columns[13]]) && $coworker_data[$indices[self::$stope_columns[13]]] !== "0") {

                $comment = array('PersNr' => $coworker_data[$indices[self::$stope_columns[0]]],
                    'Bemerkungen' => $coworker_data[$indices[self::$stope_columns[13]]]);
                $comment_arr[] = $comment;
            }


            $requirement = self::seperate_requirements($coworker_data[$indices[self::$stope_columns[14]]]);

            if (isset($requirement['Tag']) && isset($requirement['Requirement'])) {
                $requirement_arr[] = array('Tag' => $requirement['Tag'],
                    'Requirement' => $requirement['Requirement'],
                    'PersNr' => $coworker_data[$indices[self::$stope_columns[0]]]
                );
            }

            $requirement = self::seperate_requirements($coworker_data[$indices[self::$stope_columns[15]]]);

            if (isset($requirement['Tag']) && isset($requirement['Requirement'])) {
                $requirement_arr[] = array('Tag' => $requirement['Tag'],
                    'Requirement' => $requirement['Requirement'],
                    'PersNr' => $coworker_data[$indices[self::$stope_columns[0]]]
                );
            }

            $requirement = self::seperate_requirements($coworker_data[$indices[self::$stope_columns[16]]]);

            if (isset($requirement['Tag']) && isset($requirement['Requirement'])) {
                $requirement_arr[] = array('Tag' => $requirement['Tag'],
                    'Requirement' => $requirement['Requirement'],
                    'PersNr' => $coworker_data[$indices[self::$stope_columns[0]]]
                );
            }


            //If a teamleader for this team already has been found
            if (!isset($teamleader_arr[$coworker_data[$indices[self::$stope_columns[8]]]])) {
                $teamleader_arr_tmp[$coworker_data[$indices[self::$stope_columns[8]]]] = $coworker_data[$indices[self::$stope_columns[17]]];
            } else if ($teamleader_arr[$coworker_data[$indices[self::$stope_columns[8]]]] !== $coworker_data[$indices[self::$stope_columns[17]]]) {//else check if the entry differs from the existing one, this is wrong, there should be only one teamleader for one team
                echo 'Error: different teamleaders entered for one team!<br>';
            }
        }

        if ($zaehle_error > $zeile) {
            return FALSE;
        }

        //Cleanup
        // Here may be an substitution of 0 values
        $jobcluster_arr = array_unique($jobcluster_arr);
        $expertgroup_arr = array_unique($expertgroup_arr);
        $location_arr = array_unique($location_arr);
        $contact_type_arr = array_unique($contact_type_arr);
        $department_arr = array_unique($department_arr);
        $cost_center_arr = array_unique($cost_center_arr);

        $team_arr = NULL;
        foreach ($team as $team_name => $data) {
            $team_arr[] = array('OrgE-E6' => $team_name, 'OrgE-E5' => $data['department'], 'Costcenter' => $data['costcenter']);
        }


        foreach ($teamleader_arr_tmp as $team => $teamleader) {
            $teamleader_arr[] = array('OrgE-E6' => $team, 'Name' => $teamleader);
        }

        $result_array = array();

        //jobcluster
        $sql_array = $this->coworker_data->select_jobcluster();
        $result_array = array_diff($jobcluster_arr, $sql_array);
        $this->coworker_data->insert_jobcluster($result_array);

        //expertgroups
        $sql_array = $this->coworker_data->select_expertgroups();
        $result_array = array_diff($expertgroup_arr, $sql_array);
        $this->coworker_data->insert_expertgroups($result_array);

        $this->coworker_data->insert_locations($location_arr);

        //contact_types
        $sql_array = $this->coworker_data->select_contact_types();
        $result_array = array_diff($contact_type_arr, $sql_array);
        $this->coworker_data->insert_contact_types($result_array);

        //departments
        $sql_array = $this->coworker_data->select_departments();
        $result_array = array_diff($department_arr, $sql_array);
        $this->coworker_data->insert_departments($result_array);

        //cost_center
        $sql_array = $this->coworker_data->select_cost_center();
        $result_array = array_diff($cost_center_arr, $sql_array);
        $this->coworker_data->insert_cost_center($result_array);

        //teams
        //$sql_array = $this->coworker_data->select_teams();
        //$result_array = array_diff($team_arr, $sql_array);
        $this->coworker_data->insert_teams($team_arr);

        //contacts
        $inserted_ids = $this->coworker_data->insert_contacts($contact_arr);


        $this->coworker_data->insert_coworkers($coworker_arr, $inserted_ids);
        $this->coworker_data->insert_teamroles($teamrole_arr);
        $this->coworker_data->set_teamleaders($teamleader_arr);

        //comments
        //$sql_array = $this->coworker_data->select_comments();
        //$result_array = array_diff($comment_arr, $sql_array);
        $this->coworker_data->insert_comments($comment_arr);

        //tags
        $sql_array = $this->coworker_data->select_tags();
        $result_array = array_diff(self::$tags, $sql_array);
        $this->coworker_data->insert_tags($result_array);

        $this->coworker_data->insert_requirements($requirement_arr);
        $this->coworker_data->map_requirement_course();


        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return FALSE;
        } else {
            $this->db->trans_complete();
            return TRUE;
        }
    }

    /**
     * This function reads the data given in the file described by $this->data and parses it.
     *
     * The function reads the data, validates the header, and parses the file. rows that are not valid get skipped
     *
     * @author:	Marco Dennstädt
     * @param:	string	Flag which describes the data, currently 'catalogue' and 'stope' is possible
     *
     */
    private function parseData($filetype = 'catalogue') {
        $returning = NULL;

        $this->load->helper('file');

        //Read File
        $file_content = read_file($this->data['full_path']);
        // Build DOM
        $dom = DOMDocument::loadXML($file_content);
        // Select Rows from DOM
        $rows = $dom->getElementsByTagName('Row');

        // Set Variables
        $first_row = TRUE;
        $table_head = NULL;
        $table_rows = NULL;

        // Split Object to rows
        foreach ($rows as $row) {
            // Split to Cells
            $cells = $row->getElementsByTagName('Cell');
            // If this is the Table Header
            if ($first_row) {
                //Skip empty rows to find the header
                if ($cells->length == '0')
                    continue;

                foreach ($cells as $cell) {
                    $table_head[] = $cell->nodeValue;
                }
                // Set Table Header Variable to 'False'
                $first_row = FALSE;
            } else {
                foreach ($cells as $cell) {
                    $table_row[] = $cell->nodeValue;
                }
                if (isset($table_row)) {
                    $table_rows[] = $table_row;
                    $table_row = NULL;
                }
            }
        }

        $indices = $this->check_format($table_head, $filetype);

        if (isset($indices) && isset($table_rows)) {
            $returning = array('data' => $table_rows,
                'indices' => $indices);
        }


        return $returning;
    }

    /**
     * This function parses the catalogue data and sends it to the mode to insert it
     *
     * @author:	Felix Busch
     * @author: Marco Dennstädt	- 15.10.2012 - dynamic index
     * @author: Marco Dennstädt	- 18.12.2012 - cleanup, import to slow
     * @param: array	catalogue data
     */
    private function insert_catalogue_data($data, $indices) {
        if (!(isset($data) && isset($indices))) {
            return FALSE;
        }//else everything is okay

        $this->load->helper('file');
        $this->load->model('import/coworker_data');
        $this->load->model('import/import_model');

        $params = array('Katalogimport', $this->tank_auth->get_email());



        if (NULL === $indices) {
            $this->errors[] = 'Falsches Datenformat!';
            return FALSE;
        }

        $counter = 0;
        $imp_valid = TRUE;

        $costtype = Model\Costtype::find_by_name('Schulungskosten', FALSE);

        if (!$costtype) {
            $costtype = new Model\Costtype();
            $costtype->name = 'Schulungskosten';
            $success = $costtype->save();
            if (!$success) {
                $this->errors[] = 'Fehler beim Erstellen des Kostentyps "Schulungskosten"';
                return FALSE;
            }

            $costtype = Model\Costtype::last_created();
        }

        $cluster_arr = array();
        $faculty_arr_tmp = array();
        $course_arr = array();
        $pricecategory_arr = array();
        $course_faculty_arr = array();
        $course_pricecategory_arr = array();

        $pricecategory_ids = array();
        $faculty_ids = array();
        $course_ids = array();

        for ($i = 0; $i < sizeof($data); ++$i) {
            // Prepping Data
            $course_title = $data[$i][$indices[self::$catalogue_columns[0]]];


            $fachthema_requirement = self::seperate_requirements($course_title);
            $course_title = $fachthema_requirement['Requirement'];

            $faculty = $data[$i][$indices[self::$catalogue_columns[1]]];
            $faculty_code = $data[$i][$indices[self::$catalogue_columns[2]]];
            $preiskategorie = $data[$i][$indices[self::$catalogue_columns[3]]];
            $preis = $data[$i][$indices[self::$catalogue_columns[4]]];

            if ($course_title != '') {
                $faculty_arr_tmp[$faculty_code] = $faculty;
                $course_arr[] = array('title' => $course_title,
                    'price' => $preis);
                $pricecategory_arr[] = $preiskategorie;
                $course_faculty_arr[] = array('faculty' => $faculty, 'course' => $course_title);
                $course_pricecategory_arr[] = array('pricecategory' => $preiskategorie, 'course' => $course_title);
            }
        }

        $pricecategory_arr = array_unique($pricecategory_arr);

        foreach ($pricecategory_arr as $pricecategory) {
            $new_pricecategory = Model\Pricecategory::find_by_name($pricecategory, FALSE);
            if (!$new_pricecategory) {
                $new_pricecategory = new Model\Pricecategory();
                $new_pricecategory->name = $pricecategory;
                $new_pricecategory->save();
                $new_pricecategory = Model\Pricecategory::last_created();
            }

            $pricecategory_ids[$pricecategory] = $new_pricecategory->id;
        }

        foreach ($faculty_arr_tmp as $code => $faculty) {
            $new_faculty = Model\Faculty::find_by_title($faculty, FALSE);
            if (!$new_faculty) {
                $new_faculty = new Model\Faculty();
                $new_faculty->title = $faculty;
                $new_faculty->code = $code;
                $new_faculty->save();
                $new_faculty = Model\Faculty::last_created();
            }

            $faculty_ids[$faculty] = $new_faculty->id;
        }

        foreach ($course_arr as $course) {
            $new_course = Model\Course::find_by_title($course['title'], FALSE);

            if (!$new_course) {
                if ($course['price'] !== NULL) {
                    $new_cost = new Model\Cost();
                    $new_cost->amount = $course['price'];
                    $new_cost->costtype_id = $costtype->id;
                    $new_cost->save();
                    $new_cost = Model\Cost::last_created();
                }

                $new_course = new Model\Course();
                $new_course->title = $course['title'];
                if ($new_cost)
                    $new_course->cost_id = $new_cost->id;
                $success = $new_course->save();
                $new_course = Model\Course::last_created();
            }

            $course_ids[$course['title']] = $new_course->id;
        }

        foreach ($course_pricecategory_arr as $course_pricecategory) {
            $new_course_pricecategory = Model\Course\Pricecategory::where(array('course_id' => $course_ids[$course_pricecategory['course']],
                        'pricecategory_id' => $pricecategory_ids[$course_pricecategory['pricecategory']]))
                    ->all(FALSE);
            if ($new_course_pricecategory)
                continue;

            $new_course_pricecategory = new Model\Course\Pricecategory();
            $new_course_pricecategory->course_id = $course_ids[$course_pricecategory['course']];
            $new_course_pricecategory->pricecategory_id = $pricecategory_ids[$course_pricecategory['pricecategory']];
            $new_course_pricecategory->save();
        }

        foreach ($course_faculty_arr as $course_faculty) {
            $new_faculty_course = Model\Faculty\Course::where(array(
                        'course_id' => $course_ids[$course_faculty['course']],
                        'faculty_id' => $faculty_ids[$course_faculty['faculty']]))
                    ->all(FALSE);
            if ($new_faculty_course)
                continue;

            $new_faculty_course = new Model\Faculty\Course();
            $new_faculty_course->course_id = $course_ids[$course_faculty['course']];
            $new_faculty_course->faculty_id = $faculty_ids[$course_faculty['faculty']];
            $new_faculty_course->save();
        }

        return $imp_valid;
    }

    /**
     * 	The function checks, if the right Table Format is given
     *
     * This function checks if the right table format is given by comparing it with a static
     * array of this controller
     *
     * 	@author:	Felix Busch
     * @author: 	Marco Dennstädt - 15.10.2012
     *
     * @param:	array	Array with strings, each cell of the table head
     * @param: string	Flag which describes the data, currently 'catalogue' and 'stope' is possible
     */
    private function check_format($table_head, $filetype) {
        //Check TableHead
        /**
         * The tablehead should contain several collums.
         * This is one step to be sure that the data is valid.
         * Coworker_data::$collumn_names contains all necessary collumnnames
         * 	While we're checking the head we're also fetching the indices of the collumns
         * If the head is correct, we will start the insert
         */
        $indicies = NULL;
        $columns = NULL;


        switch ($filetype) {
            case 'catalogue': $columns = self::$catalogue_columns;
                break;
            case 'stope' : $columns = self::$stope_columns;
                break;
        }

        if (isset($columns)) {
            foreach ($columns as $collumn_name) {
                $index = array_search($collumn_name, $table_head);
                if ($index !== FALSE) {
                    $indicies[$collumn_name] = $index;
                }
            }
        }//else do nothing, indices stays null -> return null
        //If all collumns were found
        if (count($columns) !== count($indicies)) {
            $indicies = NULL;
        }

        return $indicies;
    }

}

/* End of file import.php */
/* Location: ./application/controllers/import.php */